package com.srit.system.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.srit.core.bean.Page;
import com.srit.system.bean.Url;
import com.srit.system.bean.UrlParent;
import com.srit.system.dao.UrlDao;

@Service
@Transactional
public class UrlDaoImpl implements UrlDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	@Override
	public List<Url> listByAll() {
		// TODO Auto-generated method stub
		String sql ="select * from TC_URL ";
		String order  = "order by ORDERNO ASC";
		List<Url> result = jdbcTemplate.query(sql+order, new UrlRowMapper());
		return result;
	}
	@Override
	public void page(Page page) {
		String sqlCount = "select count(*) from TC_URL where 1=1 ";
		String sqlAnd = " ";
		String sqlSelect = "select t2.ID,t2.NAME,t2.URL,t2.ICON,t2.PARENTID,t2.ENABLED,t2.ORDERNO,t2.GROUNPID"
				+ sqlAnd + "from (select rownum r,t1.* from TC_URL t1 where rownum<? "
				+ sqlAnd + ") t2 where t2.r>?";
		int count = jdbcTemplate.queryForInt(sqlCount + sqlAnd);
		List<Url> list = jdbcTemplate.query(sqlSelect, new Object[] {
				(page.getPageNum()+1)*page.getPageSize(),page.getPageNum()*page.getPageSize() }, new UrlRowMapper());
		page.setCount(count);
		page.setList(list);
	}
	@Override
	public int getMaxId() {
		String sql = "select MAX(ID) form TC_URL";
		return jdbcTemplate.queryForInt(sql);
	}
	@Override
	public void save(Url t) {
		String sql = "insert into TC_URL(ID,URL,NAME,ICON,PARENTID,enabled,ORDERNO) values(SEQ_TC_URL.nextval,?,?,?,?,?,?)";
		jdbcTemplate.update(sql,new Object[]{
				t.getUrl(),t.getName(),
				t.getIcon(),t.getParentId(),
				t.getEnabled(),t.getOrderNo()
		});
		
	}
	@Override
	public void update(Url t) {
		String sql = "update TC_URL set (ID,URL,NAME,ICON,PARENTID) values(SEQ_TC_URL.nextval,?,?,?)";
		jdbcTemplate.update(sql,new Object[]{
				t.getUrl(),t.getName(),
				t.getIcon(),t.getParentId()
		});
		
	}
	@Override
	public Url getById(Integer id) {
		String sql = "";
		return null;
	}
	@Override
	public void delete(Integer id) {
		// TODO Auto-generated method stub
		
	}
	@Override
	public List<UrlParent> listIsParent(String url[]) {
		String sql ="select * from TC_URL where ENABLED = 'Y'  and id =";
		List<UrlParent> urlP = new ArrayList<UrlParent>();
		for(int i = 0;i<url.length;i++){
			Url e = (Url)jdbcTemplate.queryForObject(sql+url[i],new UrlRowMapper());
			UrlParent p = new UrlParent();
			if(e.getParentId() == 0){
				p.setId(e.getId());
				p.setName(e.getName());
				urlP.add(p);
			}
		}
		for(UrlParent ul:urlP){
			List<Url> list = new ArrayList<Url>();
			for(int i = 0;i<url.length;i++){
				Url e = (Url)jdbcTemplate.queryForObject(sql+url[i],new UrlRowMapper());
				if(e.getParentId() != 0&&e.getParentId() == ul.getId()){
					list.add(e);
				}
			}
			ul.setChildrenUrl(list);
		}

		return urlP;
	}
	@Override
	public List<Url> listBySearch(String[] url) {
		String sql ="select * from TC_URL where id = ";
		List<Url> result = new ArrayList<Url>();
		for(int i = 0;i<url.length;i++){
			Url e = (Url)jdbcTemplate.queryForObject(sql+url[i],new UrlRowMapper());
			result.add(e);
		}
		return result;
	}
	@Override
	public List<Url> getChildUrlS(int id) {
		String sql ="select * from TC_URL where ENABLED = 'Y' and ISPARENT = 'N' and parentid =";
		List ch = jdbcTemplate.queryForList(sql+id);
		return ch;
	}
	
}

class UrlResultSetExtractor implements ResultSetExtractor {
	@Override
	public Object extractData(ResultSet rs) throws SQLException,
			DataAccessException {
		Url url = new Url();
		url.setUrl(rs.getString("URL"));
		url.setId(rs.getInt("ID"));
		url.setName(rs.getString("NAME"));
		url.setIcon(rs.getString("ICON"));
		url.setParentId(rs.getInt("PARENTID"));
		url.setEnabled(rs.getString("ENABLED"));
		url.setOrderNo(rs.getString("ORDERNO"));
		url.setGrounpId(rs.getInt("GROUNPID"));
		return url;
	}

}

class UrlRowMapper implements RowMapper {

	@Override
	public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		UrlResultSetExtractor extractor = new UrlResultSetExtractor();
		return extractor.extractData(rs);
	}

}